﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace codeding.KMeans.DataStructures.Lists
{
    public class UserAccount : DoiTuong
    {
        #region int UserID property
        public int UserID
        {
            get
            {
                if (_DataRow["UserID"] is int) return (int)_DataRow["UserID"];
                return -1;
            }
            set
            {
                _DataRow["UserID"] = value;
            }
        }
        #endregion

        #region string UserName property
        public string UserName
        {
            get
            {
                if (_DataRow["UserName"] is string) return (string)_DataRow["UserName"];
                return "";
            }
            set
            {
                _DataRow["UserName"] = value;
            }
        }
        #endregion

        #region string PassWord property
        public string PassWord
        {
            get
            {
                if (_DataRow["PassWord"] is string) return (string)_DataRow["PassWord"];
                return "";
            }
            set
            {
                _DataRow["PassWord"] = value;
            }
        }
        #endregion

        #region string Email property
        public string Email
        {
            get
            {
                if (_DataRow["Email"] is string) return (string)_DataRow["Email"];
                return "";
            }
            set
            {
                _DataRow["Email"] = value;
            }
        }
        #endregion

        #region string FullName property
        public string FullName
        {
            get
            {
                if (_DataRow["FullName"] is string) return (string)_DataRow["FullName"];
                return "";
            }
            set
            {
                _DataRow["FullName"] = value;
            }
        }
        #endregion

        #region bool Status property
        public bool Status
        {
            get
            {
                if (_DataRow["Status"] is bool) return (bool)_DataRow["Status"];
                return true;
            }
            set
            {
                _DataRow["Status"] = value;
            }
        }
        #endregion

        #region bool IsAdmin property
        public bool IsAdmin
        {
            get
            {
                if (_DataRow["IsAdmin"] is bool) return (bool)_DataRow["IsAdmin"];
                return false;
            }
            set
            {
                _DataRow["IsAdmin"] = value;
            }
        }
        #endregion

        public UserAccount(DataRow dataRow):base(dataRow)
        {
        }
    }

    public class DanhMucUserAccount : DanhMucDoiTuong, IEnumerable
    {
        #region Instance property: Singleton Design pattern
        private static DanhMucUserAccount _Instance;
        public static DanhMucUserAccount Instance
        {
            get
            {
                //if (_Instance == null)
                //{
                    _Instance = new DanhMucUserAccount();
                    _Instance.DataView = _Instance.DataTable.DefaultView;
                    _Instance.DataView.Sort = "";
                //}

                return _Instance;
            }
        }
        #endregion

        #region override TableName property
        public override string TableName
        {
            get
            {
                return "tblUserAccount";
            }
        }
        #endregion 

        #region Methods required by IEnumerable (use for foreach)
        public IEnumerator GetEnumerator()
        {
            return new TokenEnumerator(this);
        }

        private class TokenEnumerator : IEnumerator
        {
            private int _Position = -1;
            DanhMucDoiTuong _Dstk;

            public TokenEnumerator(DanhMucDoiTuong dstk)
            {
                _Dstk = dstk;
            }

            // Declare the MoveNext method required by IEnumerator:
            public bool MoveNext()
            {
                if (_Position < _Dstk.DataView.Count - 1)
                {
                    _Position++;
                    return true;
                }
                else
                {
                    return false;
                }
            }

            // Declare the Reset method required by IEnumerator:
            public void Reset()
            {
                _Position = -1;
            }

            // Declare the Current property required by IEnumerator:
            public object Current
            {
                get
                {
                    return new UserAccount((_Dstk.DataView[_Position].Row));
                }
            }
        }
        #endregion

        public UserAccount this[int index]
        {
            get
            {
                if (0 <= index && index <= this.Count)
                {
                    return new UserAccount(DataTable.Rows[index]);
                }
                else
                {
                    return null;
                }
            }
        }

        public UserAccount NewDoiTuong()
        {
            UserAccount doiTuong = new UserAccount(DataTable.NewRow());
            return doiTuong;
        }

        public UserAccount GetByProperty(string property, object value)
        {
            try
            {
                DataRow[] rows = GetDataRows(property, value);
                if (rows.Length > 0) return new UserAccount(rows[0]);
                else return null;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public UserAccount GetByID(int objID)
        {
            return GetByProperty("UserID", objID);
        }
        public UserAccount GetByTen(string ten)
        {
            return GetByProperty("MovieName", ten);
        }

        public DanhMucUserAccount Filter(string filterExpression)
        {
            try
            {
                DanhMucUserAccount obj = new DanhMucUserAccount();
                obj.DataView = new DataView(DanhMucUserAccount.Instance.DataTable, filterExpression, "", DataViewRowState.CurrentRows);
                return obj;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static DanhMucUserAccount GetAll()
        {
            try
            {
                return Instance;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        //Ra Rating trung bình
        public static double DanhGiaTrungBinhByUser(int userID)
        {
            SqlConnection con = new SqlConnection(DoiTuong._ConnectionString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
               
                cmd.CommandText = string.Format("SELECT SUM(Rating)/COUNT(Rating) 'value' FROM tblRating \n"+
                                                "WHERE UserID = {0}", userID);

                SqlDataAdapter da = new SqlDataAdapter();
                DataTable dt = new DataTable();
                da.SelectCommand = cmd;
                da.Fill(dt);

                double result = 0;
                if (dt != null && dt.Rows.Count == 1)
                {
                    if (double.TryParse(dt.Rows[0]["value"].ToString(), out result))
                        return result;
                }
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        //Đầu ra là danh sách các phim được đánh giá bởi cả 2 users
        public static int[] GetMovieIDDanhGiaBoiCa2User(int userID1, int userID2)
        {
            SqlConnection con = new SqlConnection(DoiTuong._ConnectionString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = string.Format("SELECT A.MovieID FROM tblRating A INNER JOIN tblRating B ON A.MovieID = B.MovieID  \n" +
                                                "WHERE A.UserID = {0} AND B.UserID = {1} ", userID1, userID2);

                SqlDataAdapter da = new SqlDataAdapter();
                DataTable dt = new DataTable();
                da.SelectCommand = cmd;
                da.Fill(dt);

                int[] result = new int[0];
                int i = 0;
                if (dt != null)
                {
                    result = new int[dt.Rows.Count];
                    foreach (DataRow row in dt.Rows)
                    {
                        if (row["MovieID"] is int) result[i++] = (int)row["MovieID"];
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        //Đầu ra là danh sách các phim được đánh giá bởi userID nhập vào
        public static int[] GetMovieIDByUserID(int userID)
        {
            SqlConnection con = new SqlConnection(DoiTuong._ConnectionString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = string.Format("SELECT MovieID FROM tblRating WHERE UserID = {0}", userID);

                SqlDataAdapter da = new SqlDataAdapter();
                DataTable dt = new DataTable();
                da.SelectCommand = cmd;
                da.Fill(dt);

                int[] result = new int[0];
                int i = 0;
                if (dt != null)
                {
                    result = new int[dt.Rows.Count];
                    foreach (DataRow row in dt.Rows)
                    {
                        if (row["MovieID"] is int) result[i++] = (int)row["MovieID"];
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        //Lấy ID lớn nhất ra
        public static int GetMaxID()
        {
            SqlConnection con = new SqlConnection(DoiTuong._ConnectionString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = string.Format("select MAX(UserID) 'value' from tblUserAccount");

                SqlDataAdapter da = new SqlDataAdapter();
                DataTable dt = new DataTable();
                da.SelectCommand = cmd;
                da.Fill(dt);

                int result = 0;
                if (dt != null && dt.Rows.Count == 1)
                {
                    if (int.TryParse(dt.Rows[0]["value"].ToString(), out result))
                        return result;
                }
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }
    }
}